#_*_coding:utf8_*_
import pandas as pd
import numpy as np
import openpyxl
import xlwt
import xlrd
# 所需配置参数   注:生成名称为excel1开始的序列名称，
# fileName : 所需读取文件名（文件必须存在与python文件夹下）必须准确，否则编译会报错
# sheetName : 所需读取文件中拆分sheet序列（默认第一个）必须准确，否则编译会报错
# rowsAll : 当前所需拆分工作簿中总行数（行数为excel左侧自带序列）必须准确，否则编译会报错
# cellAll : 所需拆分总列数（列数为excel自带序列）必须准确，否则编译会报错
# rowsPower : 所需拆分行数 必须准确，否则编译会报错
# fileName = './总.xlsx'
# sheetName = 1
# rowsAll = 158514
# cellAll = 1
# rowsPower = 50

fileName = './待激活.xlsx'
sheetName = 1
rowsAll = 196198
cellAll = 1
rowsPower = 9990

# 逻辑处理 --------------------------------------
  # 获取表与sheet
data = xlrd.open_workbook(fileName)
table = data.sheets()[sheetName - 1]
title = table.row_values(0)
  # 获取对应行数
rowsArrAll = (rowsAll // rowsPower) + 1 if ((rowsAll % rowsPower) > 0) else (rowsAll // rowsPower) # 求整数
rowsArrSurplus = rowsAll % rowsPower # 求余数
creatExcelNameCount = 1 # 创建excel序列名称

for creatExcel in range(0,rowsArrAll):
  start = (creatExcel * rowsPower) + 1 # 开始行数
  if creatExcel == (rowsArrAll - 1):
    end = start + rowsArrSurplus - 1 # 结束行数
    rowsPowerSize = rowsArrSurplus - 1 # 当前数组内行数
  else:
    end = ((creatExcel + 1) * rowsPower) + 1 # 结束行数
    rowsPowerSize = rowsPower # 当前数组内行数
  # 获取当前创建excel所需行数范围成功，继续执行
  rows=end-start
  list_values=[]
  list_values.append(title)
  for x in range(start,end):
    values=[]
    row =table.row_values(x)
    for i in range(0,cellAll):
      values.append(row[i])
    list_values.append(values)
  datamatrix=np.array(list_values)
  # 创建excel，并写入数据
  name = 'excel' + str(creatExcelNameCount) + '.xlsx'
  creatExcelNameCount = creatExcelNameCount + 1
  workbook = xlwt.Workbook()
  sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)
  for r in range(0,rowsPowerSize + 1): # 表格写入，行循环
    for c in range(0,cellAll): # 表格写入，列循环
      sheet1.write(r,c,datamatrix[r][c])
  workbook.save(name)

# 执行完成
print('success!')